package com.tcfvic.server.upload;

import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.ProgressListener;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;

import com.tcfvic.client.PersonService;
import com.tcfvic.client.pojos.Jsp;
import com.tcfvic.client.pojos.Person;
import com.tcfvic.client.pojos.Personjsp;
import com.tcfvic.client.pojos.PersonjspId;
import com.tcfvic.server.PersonServiceImpl;

public class JSAPUpload extends HttpServlet implements IExcelFormat {
	private static final long serialVersionUID = 5567237221724423872L;

	private static final Logger logger = Logger.getLogger(JSAPUpload.class);

	private List<String> errorList = new ArrayList<String>();
	private int numberImported = 0;

	private Map<Integer, Person> persons;
	private Map<Integer, Jsp> jsps;
	private PersonService personService = new PersonServiceImpl();

	public JSAPUpload() {
		logger.info("Uploading JSAP details.");
		personService = new PersonServiceImpl();
		initialise();
	}

	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		response.setContentType("text/plain");
		logger.info("Inside JSAP File Upload Servlet...............>>>>>>>>>>");
		personService = new PersonServiceImpl();
		initialise();
		try {
			getFileItem(request);
		} catch (Exception e) {
			logger.error(e);
		}
	}

	@SuppressWarnings("unchecked")
	private FileItem getFileItem(final HttpServletRequest request) {
		FileItemFactory factory = new DiskFileItemFactory();
		ServletFileUpload upload = new ServletFileUpload(factory);
		// Create a progress listener, if want to use
		ProgressListener progressListener = new ProgressListener() {
			private long megaBytes = -1;
			int i = -1;

			public void update(long pBytesRead, long pContentLength, int pItems) {
				if (i != pItems) {
					i = pItems;
				}
				long mBytes = pBytesRead / 10000;
				if (megaBytes == mBytes) {
					return;
				}
				megaBytes = mBytes;
			}
		};
		upload.setProgressListener(progressListener);

		try {
			List items = upload.parseRequest(request);
			Iterator it = items.iterator();
			while (it.hasNext()) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField()) {
					this.processUpload(item);
				}
			}
		} catch (Exception e) {
			logger.error(e);
			return null;
		}
		return null;
	}

	private void processUpload(FileItem uploadItem) {
		try {
			String uploadDir = getServletContext().getRealPath("/")
					+ "tcfuaUplaod/";
			File file = new File(uploadDir);
			file.mkdirs();

			InputStream is = uploadItem.getInputStream();
			String filename = String.valueOf(System.currentTimeMillis())
					+ ".xls";
			File uploadedFile = new File(uploadDir + "/" + filename);
			DataOutputStream out = null;
			out = new DataOutputStream(new BufferedOutputStream(
					new FileOutputStream(uploadedFile)));
			while (true) {
				int value = is.read();
				out.write(value);
				if (value < 0) {
					break;
				}
			}
			out.close();
			uploadFile(uploadedFile);

		} catch (IOException e) {
			logger.error(e);
		}
	}

	public void initialise() {
		List<Person> personResultList = personService.getAllPersonDetails();
		persons = new HashMap<Integer, Person>();
		for (Person person : personResultList) {
			persons.put(person.getId(), person);
		}
		List<Jsp> jspResultList = personService.getAllJSPs();
		jsps = new HashMap<Integer, Jsp>();
		for (Jsp jsp : jspResultList) {
			jsps.put(jsp.getId(), jsp);
		}
	}

	public static void main(String... args) {
		JSAPUpload f = new JSAPUpload();
		f.uploadFile(new File(
				"c:/temp/FINAL - JSAP Master.xls"));
	}

	public void uploadFile(File file) {
		logger.info("In ExmpStatusUpload.uploadFile");

		try {
			Workbook workbook = createWorkbook(file);
			if (workbook.getSheets() != null) {
				processSheets(workbook);
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}

	public void processSheets(Workbook workbook) throws Exception {
		Sheet sheet = workbook.getSheet(0);
		int i = 1;
		while (true) {
			i++;
			if (sheet.getCell("A" + i) != null
					&& !sheet.getCell("A" + i).getContents().isEmpty()) {
				try {
					Person personNameAlreadyInDatabase = getNameIfPersonExists(sheet
							.getRow(i - 1));
					if (personNameAlreadyInDatabase == null) {
						continue;
					}

					processEmploymentStatus(personNameAlreadyInDatabase, sheet
							.getRow(i - 1));
					numberImported++;
				} catch (Exception e) {
					logger
							.error(
									"An error occured processing employment status ",
									e);
				}
			} else {
				break;
			}
		}
	}

	private void processEmploymentStatus(Person person, Cell[] row)
			throws Exception {
		int colCount = 5;

		logger.info("Importing " + person.getFirstname() + " "
				+ person.getSurname());

		String registered = null;
		String JSAP = null;
		String street = null;
		String suburb = null;
		String state = null;
		String postcode = null;
		String phone = null;
		String website = null;
		String email = null;
		String notes = null;

		String jobSeekId;
		Cell dateReg;
		String regStream;
		String consultName;
		String consultEmail;
		String consultPhone;
		String consultMob;
		String managerName;
		String managerEmail;
		String managerPhone;
		String managerMobile;
		String personNotes;

		try {
			registered = getStringFromCell(row, ++colCount);
			jobSeekId = getStringFromCell(row, ++colCount);
			dateReg = getCell(row, ++colCount);
			regStream = getStringFromCell(row, ++colCount);
			JSAP = getStringFromCell(row, ++colCount);
			street = getStringFromCell(row, ++colCount);
			suburb = getStringFromCell(row, ++colCount);
			state = getStringFromCell(row, ++colCount);
			postcode = getStringFromCell(row, ++colCount);
			phone = getStringFromCell(row, ++colCount);
			email = getStringFromCell(row, ++colCount);
			website = getStringFromCell(row, ++colCount);
			notes = getStringFromCell(row, ++colCount);
			Jsp jsp = getJsp(JSAP, suburb);
			if (jsp == null) {
				jsp = new Jsp();
				jsp.setName(JSAP);
				jsp.setStreet(street);
				jsp.setSuburb(suburb);
				jsp.setState(state);
				jsp.setWebsite(website);
				try {
					jsp.setPostcode(Integer.valueOf(postcode));
				} catch (Exception e) {
				}
				jsp.setPhone(phone);
				jsp.setStreet2(email);
				jsp.setNotes(notes);
				personService.saveJSPDetails(jsp);
				initialise();
				jsp = getJsp(JSAP, suburb);
			}

			consultName = getStringFromCell(row, ++colCount);
			consultEmail = getStringFromCell(row, ++colCount);
			consultPhone = getStringFromCell(row, ++colCount);
			consultMob = getStringFromCell(row, ++colCount);
			managerName = getStringFromCell(row, ++colCount);
			managerEmail = getStringFromCell(row, ++colCount);
			managerPhone = getStringFromCell(row, ++colCount);
			managerMobile = getStringFromCell(row, ++colCount);
			personNotes = getStringFromCell(row, ++colCount);

			Personjsp p = new Personjsp();
			p.setId(new PersonjspId(person.getId(), jsp.getId()));
			p.setJspid(jobSeekId);
			if (dateReg != null) {
				try {
					p.setDateofregistration(((DateCell) dateReg).getDate());
				} catch (Exception e) {
				}
			}
			p.setRegistrationstream(regStream);
			p.setConsultantsname(consultName);
			p.setConsultantsemail(consultEmail);
			p.setConsultantsphone(consultPhone);
			p.setConsultantmobile(consultMob);
			p.setManagersname(managerName);
			p.setManagersemail(managerEmail);
			p.setManagerphone(managerPhone);
			p.setManagermobile(managerMobile);
			p.setNotes(personNotes);
			p.setRegistered(registered != null
					&& registered.equals("Yes") ? "Yes"
					: "");
			personService.savePersonJSP(p, true);
		} catch (Exception e) {
			logger.warn("Error getting row data for employment status upload",
					e);
		}

	}

	private Jsp getJsp(String name, String suburb) {
		if (name == null) {
			name = "";
		}
		if (suburb == null) {
			suburb = "";
		}
		for (Jsp jsp : jsps.values()) {
			if (name != null && name.equals(jsp.getName())
					&& suburb.equals(jsp.getSuburb())) {
				return jsp;
			}
		}
		return null;
	}

	private String getStringFromCell(Cell[] row, int col) {
		try {
			return row[col].getContents().trim();
		} catch (Exception e) {
			return "";
		}
	}

	private Cell getCell(Cell[] row, int col) {
		try {
			return row[col];
		} catch (Exception e) {
			return null;
		}
	}

	private Person getNameIfPersonExists(Cell[] row) {
		String surname = fullCamelCase(row[3].getContents());
		String firstname = fullCamelCase(row[2].getContents());

		for (Person person : persons.values()) {
			if (person.getSurname().equals(surname)
					&& person.getFirstname().equals(firstname)) {
				return person;
			}
		}
		String msg = "Person not found in database: " + firstname + " "
				+ surname;
		errorList.add(msg);
		logger.warn(msg);

		return null;
	}

	private String fullCamelCase(String camelMe) {
		StringBuffer sb = new StringBuffer();
		String[] words = camelMe.split("\\s");
		for (String s : words) {
			sb.append(capitalize(s));
		}
		return sb.toString();
	}

	private static String capitalize(String s) {
		if (s.length() == 0)
			return s;
		return s.substring(0, 1).toUpperCase() + s.substring(1).toLowerCase();
	}

	public Workbook createWorkbook(File file) throws BiffException, IOException {
		return Workbook.getWorkbook(file);
	}

}
